using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
using System.Data;
using System.Collections;

namespace DatabaseAccess
{
    public class SqlDBExecution:IDisposable
    {
        #region 'Private Variables'
        private Hashtable _parainfo = null;
        private string _cnnstring = string.Empty;
        private SqlConnection _connection = null;
        private bool _inTransaction = false;
        private SqlTransaction _transaction = null;
        
        #endregion

        #region 'Internal Property and Constructor'
        internal Hashtable ParaInfo
        {
            set { _parainfo = value; }
        }
        
        internal SqlDBExecution(string cnnstring)
        {
            _cnnstring = cnnstring;
        }
        
        #endregion

        #region 'Private Methods'
        private void BuildParameters(SqlCommand command, string spname, params object[] paravalues)
        {
            const string functionname = "BuildParameters";
            try
            {
                StoredProcedure spobject = (StoredProcedure)(_parainfo)[spname.ToLower()];            
                for (int i = 0; i < spobject.ParaList.Count; i++)
                {
                    Para para = null;
                    object paravalue = null;
                    SqlParameter parameter = null;
                    para = (Para)spobject.ParaList[i];
                    paravalue = paravalues[i];
                    if (para.DataType == SqlDbType.Image)
                    {
                        parameter = new SqlParameter(para.ParaName, para.DataType);
                    }
                    else if (para.DataType == SqlDbType.Text)
                    {
                        parameter = new SqlParameter(para.ParaName, para.DataType, 0);
                    }
                    else
                    {
                        parameter = new SqlParameter(para.ParaName, para.DataType, para.Size);
                    }
                    parameter.Direction = para.ParaDirection;
                    if (paravalue == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = paravalue;
                    command.Parameters.Add(parameter);
                }
            }
            catch (Exception ex)
            {
                Exception newexception = null;
                string errorsource = string.Empty;
                string errormessage = string.Empty;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                errormessage = ex.Message;
                newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        private void CreateConnection()
        {
            _connection = new SqlConnection(_cnnstring);
            _connection.Open();
            _inTransaction = false;
        }
        #endregion

        #region 'Public Transaction Methods'
        public void StartTransation()
        {
            const string functionname = "StartTransaction";
            try
            {
                CreateConnection();
                _transaction = _connection.BeginTransaction();
                _inTransaction = true;
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
        }
        public void CommitTransation()
        {
            const string functionname = "CommitTransaction";
            try
            {
                if (_inTransaction)
                {
                    _transaction.Commit();
                    _inTransaction = false;
                    //_connection.Close();
                }
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (_transaction != null)
                {
                    if (_transaction.Connection != null)
                        _transaction.Connection.Close();
                }
                if (_connection != null)
                    _connection.Close();
            }
        }
        public void RollbackTransation()
        {
            const string functionname = "RollbackTransaction";
            try
            {
                if (_inTransaction)
                {
                    _transaction.Rollback();
                    _inTransaction = false;
                   // _connection.Close();
                }
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (_transaction != null)
                {
                    if (_transaction.Connection != null)
                        _transaction.Connection.Close();
                }
                if (_connection != null)
                    _connection.Close();
            }
        }
        public bool InTransaction()
        {
            return _inTransaction;
        }
        #endregion

        #region 'ExecuteDataset'
        public DataSet ExecuteDataset(string spname, params object[] ParamValues)
        {
            const string functionname = "ExecuteDataset";
            try
            {
                return ExecuteDataset(CommandType.StoredProcedure, spname, ParamValues);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        public DataSet ExecuteDataset(string sql)
        {

            const string functionname = "ExecuteDataset";
            try
            {
                return ExecuteDataset(CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }

        }
        private DataSet ExecuteDataset(CommandType commandtype, string spsql, params object[] paramvalues)
        {
            SqlDataAdapter da = null;
            DataSet ds = new DataSet();
            SqlCommand command = null;
            const string functionname = "ExecuteDataset";
            try
            {
                command = new SqlCommand(spsql);
                command.CommandType = commandtype;
                if (paramvalues != null)
                    BuildParameters(command, spsql, paramvalues);
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                da = new SqlDataAdapter(command);
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
            finally
            {
                if (!_inTransaction)
                {
                    if (command != null)
                    {
                        if (command.Connection != null)
                            command.Connection.Close();
                    }
                    if (_connection != null)
                        _connection.Close();
                }
            }

        }
        #endregion

        #region 'ExecuteDataReader'
        public SqlDataReader ExecuteDataReader(string spname, params object[] ParamValues)
        {
            const string functionname = "ExecuteDataReader";
            try
            {
                return ExecuteDataReader(CommandType.StoredProcedure, spname, ParamValues);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        public SqlDataReader ExecuteDataReader(string sql)
        {

            const string functionname = "ExecuteDataReader";
            try
            {
                return ExecuteDataReader(CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }

        }
        private SqlDataReader ExecuteDataReader(CommandType commandtype, string spsql, params object[] ParamValues)
        {            
            const string functionname = "ExecuteDataReader";
            try
            {
                SqlDataReader dr = null;
                SqlCommand command = null;
                command = new SqlCommand(spsql);
                command.CommandType = commandtype;
                if (ParamValues != null)
                    BuildParameters(command, spsql, ParamValues);
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                dr = command.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }

        }
        #endregion

        #region 'ExecuteScalar'
        public object ExecuteScalar(string spname, params object[] ParamValues)
        {
            const string functionname = "ExecuteScalar";
            try
            {
                return ExecuteScalar(CommandType.StoredProcedure, spname, ParamValues);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }
        }
        public object ExecuteScalar(string sql)
        {
            const string functionname = "ExecuteScalar";
            try
            {
                return ExecuteScalar(CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                Exception newexception = new Exception(errormessage);
                newexception.Source = errorsource;
                throw newexception;
            }

        }
        private object ExecuteScalar(CommandType commandtype, string spsql, params object[] ParamValues)
        {
            const string functionname = "ExecuteScalar";
            SqlCommand command = null;
            try
            {                
                command = new SqlCommand(spsql);
                command.CommandType = commandtype;
                if (ParamValues != null)
                    BuildParameters(command, spsql, ParamValues);
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (!_inTransaction)
                {
                    if (command != null)
                    {
                        if (command.Connection != null)
                            command.Connection.Close();
                    }
                    if (_connection != null)
                        _connection.Close();
                }
            }

        }
        #endregion

        #region 'Execute StoredProcedure and SQL'
        public System.Collections.ArrayList ExecuteStoredProcedureOutputValues(string spname, params object[] paramvalues)
        {
            System.Collections.ArrayList outputparams = new System.Collections.ArrayList();
            SqlCommand command = null;
            const string functionname = "ExecuteStoredProcedureOutputValues";
            try
            {
                command = new SqlCommand(spname);
                command.CommandType = CommandType.StoredProcedure;
                if (paramvalues != null)
                    BuildParameters(command, spname, paramvalues);
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                command.ExecuteNonQuery();
                foreach (System.Data.SqlClient.SqlParameter param in command.Parameters)
                {
                    if (param.Direction == ParameterDirection.Output)
                    {
                        outputparams.Add(param.Value);
                    }
                }
                return outputparams;
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (!_inTransaction)
                {
                    if (command != null)
                    {
                        if (command.Connection != null)
                            command.Connection.Close();
                    }
                    if (_connection != null)
                        _connection.Close();
                }
            }
        }
        public void ExecuteStoredProcedure(string spname, params object[] paramvalues)
        {
            const string functionname = "ExecuteStoredProcedure";
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(spname);
                command.CommandType = CommandType.StoredProcedure;
                if (paramvalues != null)
                    BuildParameters(command, spname, paramvalues);
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (!_inTransaction)
                {
                    if (command != null)
                    {
                        if (command.Connection != null)
                            command.Connection.Close();
                    }
                    if (_connection != null)
                        _connection.Close();
                }
            }
        }
        public void ExecuteSQL(string sSQL)
        {
            const string functionname = "ExecuteSQL";
            SqlCommand command = null;
            try
            {
                command = new SqlCommand(sSQL);
                command.CommandType = CommandType.Text;
                if (_inTransaction)
                {
                    command.Transaction = _transaction;
                    command.Connection = _transaction.Connection;
                }
                else
                {
                    CreateConnection();
                    command.Connection = _connection;
                }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string errorsource = null;
                string errormessage = null;
                errormessage = ex.Message;
                errorsource = this.GetType().ToString() + "." + functionname + "/" + ex.Source;
                if (_inTransaction)
                {
                    RollbackTransation();
                }
                Exception newex = new Exception(errormessage);
                newex.Source = errorsource;
                throw newex;
            }
            finally
            {
                if (!_inTransaction)
                {
                    if (command != null)
                    {
                        if (command.Connection != null)
                            command.Connection.Close();
                    }
                    if (_connection != null)
                        _connection.Close();
                }
            }
        }
        #endregion

        #region IDisposable Members

        public virtual void Close()
        {
            if (null != _connection)
                _connection.Close();
        }

        public virtual void Dispose()
        {
            Close();
            if (null != _connection)
                _connection.Dispose();
        }

        #endregion
    }
}
